declare @abnormalNum int;
declare @riskNum int;
declare @abnormalShopNum int;
declare @riskShopNum int;
declare @shopNum int;

-- 1. 判断临时表是否存在，存在则删除
if object_id('tempdb..#list') is not null
    drop table #list;

-- 2. 创建临时表并插入分组统计数据
select 
    pos.shop_id,
    model.mchnt_cd as shop_code,
    model.store_id,
    model.txn_reserved3,
    count(*) as num,
    case when count(*) >= 2 and count(*) <= 3 then 1 else 0 end as abnormal,
    case when count(*) > 3 then 1 else 0 end as risk
into #list
from bsyc_trade_stream model
inner join bsyc_shop_pos pos on model.store_id = pos.store_code
group by model.txn_reserved3, pos.shop_id, model.store_id, model.mchnt_cd;

-- 3. 统计异常和风险数量（记录级别 + 店铺级别）
select 
    @abnormalNum = sum(abnormal),
    @riskNum = sum(risk)
from #list;

-- 计算异常店铺数量（按shop_id去重，那些max(abnormal)=1的店铺）
select @abnormalShopNum = sum(case when max_abnormal > 0 then 1 else 0 end)
from (
    select shop_id, max(abnormal) as max_abnormal
    from #list
    group by shop_id
) as shop_abnormal;

-- 计算风险店铺数量（按shop_id去重，那些max(risk)=1的店铺）
select @riskShopNum = sum(case when max_risk > 0 then 1 else 0 end)
from (
    select shop_id, max(risk) as max_risk
    from #list
    group by shop_id
) as shop_risk;

SELECT @shopNum=count(*) from kehu where coalesce(tzsy,'0') ='0'

-- 4. 输出结果（包含记录数量和店铺数量）
select 
    @shopNum as  shopNum,
    @abnormalNum as abnormalNum, 
    @riskNum as riskNum,
    @abnormalShopNum as abnormalShopNum,
    @riskShopNum as riskShopNum;
-- 5. 删除临时表
drop table #list;
